05. Composite Key

Composite Key

You are familiar with primary and foreign keys in Entity Relationship Diagrams (ERDs). We will introduce a new type of key - a Composite Key - as part of the DVD Rental Database project.

Please have a copy of the ERD handy. It is available at the bottom of the page as a resource as well.

A table with a Composite Key does not have a unique key for each row in the table. Instead a combination of two or more columns serves as a unique identifier for each row.

You can read more about Composite Keys in the following link:

https://www.javatpoint.com/sql-composite-key

Here is an example from the DVD Rental Database. You can see below some sample rows from the film_actor table. We have provided two table headers, the first where the rows are sorted by film_id, and the second where the rows are sorted by actor_id.

Comparing the two table headers, you can see just using 1 column, e.g., the actor_id column, does not uniquely identify the relationship between the film and actor. Similarly, using just the film_id column does not uniquely identify the relationship. The combination of actor_id with film_id creates a combined (or composite) unique value for each row in the table. The combination of the two column values (actor_id = 1 and film_id = 1) identify the entity relationship.